﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using MySql.Data.MySqlClient;

public class ProjectBLL
{
    string connection = WebConfigurationManager.ConnectionStrings["UCNLinkDB"].ConnectionString;
    public int CreateProjectCompany(ProjectProberty pp)
    {
        int result = 0;
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("INSERT INTO tblProject (projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID) VALUES(@projectTitle, @projectContactName, @projectCompanyName, @projectMail, @projectPhoneNo, @projectReserved, @projectTimesReserved, @projectRequirements, @projectStartPeriod, @projectEndPeriod, @projectLifeSpan, @DivisionID)", conn);

        cmd.Parameters.Add("@projectTitle", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectTitle"].Value = pp.Title;

        cmd.Parameters.Add("@projectContactName", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectContactName"].Value = pp.ContactName;

        cmd.Parameters.Add("@projectCompanyName", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectCompanyName"].Value = pp.CompanyName;

        cmd.Parameters.Add("@projectMail", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectMail"].Value = pp.Email;

        cmd.Parameters.Add("@projectPhoneNo", MySqlDbType.VarChar, 15);
        cmd.Parameters["@projectPhoneNo"].Value = pp.PhoneNo;

        cmd.Parameters.Add("@projectReserved", MySqlDbType.Bit);
        cmd.Parameters["@projectReserved"].Value = pp.Reserved;

        cmd.Parameters.Add("@projectTimesReserved", MySqlDbType.Int32);
        cmd.Parameters["@projectTimesReserved"].Value = pp.TimesReserved;

        cmd.Parameters.Add("@projectRequirements", MySqlDbType.VarChar, 200);
        cmd.Parameters["@projectRequirements"].Value = pp.Requirements;

        cmd.Parameters.Add("@projectStartPeriod", MySqlDbType.DateTime);
        cmd.Parameters["@projectStartPeriod"].Value = pp.StartPeriod;

        cmd.Parameters.Add("@projectEndPeriod", MySqlDbType.DateTime);
        cmd.Parameters["@projectEndPeriod"].Value = pp.EndPeriod;

        cmd.Parameters.Add("@projectLifeSpan", MySqlDbType.DateTime);
        cmd.Parameters["@projectLifeSpan"].Value = pp.LifeSpan;

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = pp.DivisionId;

        //cmd.Parameters.Add("@SubEducationID", MySqlDbType.Int32);
        //cmd.Parameters["@SubEducationID"].Value = pp.SubEducationId;

        conn.Open();

        result = cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn.Close();
        return result;
    }

    public int CreateProjectStudent(ProjectProberty pp)
    {
        int result = 0;
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("INSERT INTO tblProject (projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, SubEducationID, DivisionID) VALUES(@projectTitle, @projectContactName, @projectCompanyName, @projectMail, @projectPhoneNo, @projectReserved, @projectTimesReserved, @projectRequirements, @projectStartPeriod, @projectEndPeriod, @projectLifeSpan, @SubEducationID, @DivisionID)", conn);

        cmd.Parameters.Add("@projectTitle", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectTitle"].Value = pp.Title;

        cmd.Parameters.Add("@projectContactName", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectContactName"].Value = pp.ContactName;

        cmd.Parameters.Add("@projectCompanyName", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectCompanyName"].Value = pp.CompanyName;

        cmd.Parameters.Add("@projectMail", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectMail"].Value = pp.Email;

        cmd.Parameters.Add("@projectPhoneNo", MySqlDbType.VarChar, 15);
        cmd.Parameters["@projectPhoneNo"].Value = pp.PhoneNo;

        cmd.Parameters.Add("@projectReserved", MySqlDbType.Bit);
        cmd.Parameters["@projectReserved"].Value = pp.Reserved;

        cmd.Parameters.Add("@projectTimesReserved", MySqlDbType.Int32);
        cmd.Parameters["@projectTimesReserved"].Value = pp.TimesReserved;

        cmd.Parameters.Add("@projectRequirements", MySqlDbType.VarChar, 200);
        cmd.Parameters["@projectRequirements"].Value = pp.Requirements;

        cmd.Parameters.Add("@projectStartPeriod", MySqlDbType.DateTime);
        cmd.Parameters["@projectStartPeriod"].Value = pp.StartPeriod;

        cmd.Parameters.Add("@projectEndPeriod", MySqlDbType.DateTime);
        cmd.Parameters["@projectEndPeriod"].Value = pp.EndPeriod;

        cmd.Parameters.Add("@projectLifeSpan", MySqlDbType.DateTime);
        cmd.Parameters["@projectLifeSpan"].Value = pp.LifeSpan;

        cmd.Parameters.Add("@SubEducationID", MySqlDbType.Int32);
        cmd.Parameters["@SubEducationID"].Value = pp.SubEducationId;

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = pp.DivisionId;

        conn.Open();

        result = cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn.Close();
        return result;
    }

    public List<ProjectProberty> GetAllProjects()
    {
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject", conn);


        List<ProjectProberty> allPP = new List<ProjectProberty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                ProjectProberty pProperty = new ProjectProberty();
                pProperty.ID = Convert.ToInt32(reader["ID"]);
                pProperty.Title = reader["projectTitle"].ToString();
                pProperty.ContactName = reader["projectContactName"].ToString();
                pProperty.CompanyName = reader["projectCompanyName"].ToString();
                pProperty.Email = reader["projectMail"].ToString();
                pProperty.PhoneNo = reader["projectPhoneNo"].ToString();
                pProperty.Reserved = Convert.ToBoolean(reader["projectReserved"]);
                pProperty.TimesReserved = Convert.ToInt32(reader["projectTimesReserved"]);
                pProperty.Requirements = reader["projectRequirements"].ToString();
                pProperty.StartPeriod = Convert.ToDateTime(reader["projectStartPeriod"]);
                pProperty.EndPeriod = Convert.ToDateTime(reader["projectEndPeriod"]);
                pProperty.LifeSpan = Convert.ToDateTime(reader["projectLifeSpan"]);
                if (!Convert.IsDBNull(reader["DivisionID"]))
                {
                    pProperty.DivisionId = Convert.ToInt32(reader["DivisionID"]);
                }
                else
                {
                    pProperty.DivisionId = 0;
                }
                if (!Convert.IsDBNull(reader["SubEducationID"]))
                {
                    pProperty.SubEducationId = Convert.ToInt32(reader["SubEducationID"]);
                }
                else
                {
                    pProperty.SubEducationId = 0;
                }
                allPP.Add(pProperty);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allPP;
    }

    public List<ProjectProberty> GetAllProjects(List<int> divIds)
    {
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject WHERE DivisionID = @DivisionID1 OR DivisionID = @DivisionID2 OR DivisionID = @DivisionID3 OR DivisionID = @DivisionID4", conn);

        int divId = 0;
        for (int i = 0; i < divIds.Count(); )
        {
            divId = divIds[i];
            i++;
            string parameter = "@DivisionID" + i;
            cmd.Parameters.Add(parameter, MySqlDbType.Int32);
            cmd.Parameters[parameter].Value = divId;

        }

        List<ProjectProberty> allPP = new List<ProjectProberty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                ProjectProberty pProperty = new ProjectProberty();
                pProperty.ID = Convert.ToInt32(reader["ID"]);
                pProperty.Title = reader["projectTitle"].ToString();
                pProperty.ContactName = reader["projectContactName"].ToString();
                pProperty.CompanyName = reader["projectCompanyName"].ToString();
                pProperty.Email = reader["projectMail"].ToString();
                pProperty.PhoneNo = reader["projectPhoneNo"].ToString();
                pProperty.Reserved = Convert.ToBoolean(reader["projectReserved"]);
                pProperty.TimesReserved = Convert.ToInt32(reader["projectTimesReserved"]);
                pProperty.Requirements = reader["projectRequirements"].ToString();
                pProperty.StartPeriod = Convert.ToDateTime(reader["projectStartPeriod"]);
                pProperty.EndPeriod = Convert.ToDateTime(reader["projectEndPeriod"]);
                pProperty.LifeSpan = Convert.ToDateTime(reader["projectLifeSpan"]);
                if (!Convert.IsDBNull(reader["DivisionID"]))
                {
                    pProperty.DivisionId = Convert.ToInt32(reader["DivisionID"]);
                }
                else
                {
                    pProperty.DivisionId = 0;
                }
                if (!Convert.IsDBNull(reader["SubEducationID"]))
                {
                    pProperty.SubEducationId = Convert.ToInt32(reader["SubEducationID"]);
                }
                else
                {
                    pProperty.SubEducationId = 0;
                }
                allPP.Add(pProperty);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allPP;
    }

    public List<ProjectProberty> SearchForProjectsByTitle(string _input, int _divId)
    {
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject WHERE projectTitle LIKE @projectTitle AND DivisionID = @DivisionID", conn);

        cmd.Parameters.Add("@projectTitle", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectTitle"].Value = "%" + _input + "%";

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = _divId;


        List<ProjectProberty> allPP = new List<ProjectProberty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                ProjectProberty pProperty = new ProjectProberty();
                pProperty.ID = Convert.ToInt32(reader["ID"]);
                pProperty.Title = reader["projectTitle"].ToString();
                pProperty.ContactName = reader["projectContactName"].ToString();
                pProperty.CompanyName = reader["projectCompanyName"].ToString();
                pProperty.Email = reader["projectMail"].ToString();
                pProperty.PhoneNo = reader["projectPhoneNo"].ToString();
                pProperty.Reserved = Convert.ToBoolean(reader["projectReserved"]);
                pProperty.TimesReserved = Convert.ToInt32(reader["projectTimesReserved"]);
                pProperty.Requirements = reader["projectRequirements"].ToString();
                pProperty.StartPeriod = Convert.ToDateTime(reader["projectStartPeriod"]);
                pProperty.EndPeriod = Convert.ToDateTime(reader["projectEndPeriod"]);
                pProperty.LifeSpan = Convert.ToDateTime(reader["projectLifeSpan"]);
                if (!Convert.IsDBNull(reader["DivisionID"]))
                {
                    pProperty.DivisionId = Convert.ToInt32(reader["DivisionID"]);
                }
                else
                {
                    pProperty.DivisionId = 0;
                }
                if (!Convert.IsDBNull(reader["SubEducationID"]))
                {
                    pProperty.SubEducationId = Convert.ToInt32(reader["SubEducationID"]);
                }
                else
                {
                    pProperty.SubEducationId = 0;
                }
                allPP.Add(pProperty);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allPP;
    }

    public List<ProjectProberty> SearchForProjectsByCompanyName(string _input, int _divId)
    {
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject WHERE projectCompanyName LIKE @projectCompanyName AND DivisionID = @DivisionID", conn);

        cmd.Parameters.Add("@projectCompanyName", MySqlDbType.VarChar, 250);
        cmd.Parameters["@projectCompanyName"].Value = "%" + _input + "%";

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = _divId;


        List<ProjectProberty> allPP = new List<ProjectProberty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                ProjectProberty pProperty = new ProjectProberty();
                pProperty.ID = Convert.ToInt32(reader["ID"]);
                pProperty.Title = reader["projectTitle"].ToString();
                pProperty.ContactName = reader["projectContactName"].ToString();
                pProperty.CompanyName = reader["projectCompanyName"].ToString();
                pProperty.Email = reader["projectMail"].ToString();
                pProperty.PhoneNo = reader["projectPhoneNo"].ToString();
                pProperty.Reserved = Convert.ToBoolean(reader["projectReserved"]);
                pProperty.TimesReserved = Convert.ToInt32(reader["projectTimesReserved"]);
                pProperty.Requirements = reader["projectRequirements"].ToString();
                pProperty.StartPeriod = Convert.ToDateTime(reader["projectStartPeriod"]);
                pProperty.EndPeriod = Convert.ToDateTime(reader["projectEndPeriod"]);
                pProperty.LifeSpan = Convert.ToDateTime(reader["projectLifeSpan"]);
                if (!Convert.IsDBNull(reader["DivisionID"]))
                {
                    pProperty.DivisionId = Convert.ToInt32(reader["DivisionID"]);
                }
                else
                {
                    pProperty.DivisionId = 0;
                }
                if (!Convert.IsDBNull(reader["SubEducationID"]))
                {
                    pProperty.SubEducationId = Convert.ToInt32(reader["SubEducationID"]);
                }
                else
                {
                    pProperty.SubEducationId = 0;
                }
                allPP.Add(pProperty);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allPP;
    }

    public ProjectProberty GetProjectByID(int proId)
    {
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject WHERE ID = @ID", conn);

        cmd.Parameters.Add("@ID", MySqlDbType.Int32);
        cmd.Parameters["@ID"].Value = proId;

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();
        ProjectProberty pProperty = new ProjectProberty();


        if (reader.Read())
        {
            pProperty.ID = Convert.ToInt32(reader["ID"]);
            pProperty.Title = reader["projectTitle"].ToString();
            pProperty.ContactName = reader["projectContactName"].ToString();
            pProperty.CompanyName = reader["projectCompanyName"].ToString();
            pProperty.Email = reader["projectMail"].ToString();
            pProperty.PhoneNo = reader["projectPhoneNo"].ToString();
            pProperty.Reserved = Convert.ToBoolean(reader["projectReserved"]);
            pProperty.TimesReserved = Convert.ToInt32(reader["projectTimesReserved"]);
            pProperty.Requirements = reader["projectRequirements"].ToString();
            pProperty.StartPeriod = Convert.ToDateTime(reader["projectStartPeriod"]);
            pProperty.EndPeriod = Convert.ToDateTime(reader["projectEndPeriod"]);
            pProperty.LifeSpan = Convert.ToDateTime(reader["projectLifeSpan"]);
            if (!Convert.IsDBNull(reader["DivisionID"]))
            {
                pProperty.DivisionId = Convert.ToInt32(reader["DivisionID"]);
            }
            else
            {
                pProperty.DivisionId = 0;
            }
            if (!Convert.IsDBNull(reader["SubEducationID"]))
            {
                pProperty.SubEducationId = Convert.ToInt32(reader["SubEducationID"]);
            }
            else
            {
                pProperty.SubEducationId = 0;
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return pProperty;
    }

    /// <summary>
    /// Finds all projects related to the specific division by its id. If subeducation is true it only returns those with subeducation id in it.
    /// </summary>
    /// <param name="divId">Id for the specific division the project is related to.</param>
    /// <param name="hasSubEd">If the project is related to a subeducation.</param>
    /// <returns></returns>
    public List<ProjectProberty> GetAllProjectsByDivisonID(int divId, bool hasSubEd)
    {

        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand();
        if (!hasSubEd)
        {
            cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject WHERE DivisionID = @DivisionID AND SubEducationID IS NULL", conn);
        }
        else
        {
            cmd = new MySqlCommand("SELECT ID, projectTitle, projectContactName, projectCompanyName, projectMail, projectPhoneNo, projectReserved, projectTimesReserved, projectRequirements, projectStartPeriod, projectEndPeriod, projectLifeSpan, DivisionID, SubEducationID FROM tblProject WHERE DivisionID = @DivisionID AND SubEducationID IS NOT NULL", conn);
        }

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = divId;

        List<ProjectProberty> allPP = new List<ProjectProberty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                ProjectProberty pProperty = new ProjectProberty();
                pProperty.ID = Convert.ToInt32(reader["ID"]);
                pProperty.Title = reader["projectTitle"].ToString();
                pProperty.ContactName = reader["projectContactName"].ToString();
                pProperty.CompanyName = reader["projectCompanyName"].ToString();
                pProperty.Email = reader["projectMail"].ToString();
                pProperty.PhoneNo = reader["projectPhoneNo"].ToString();
                pProperty.Reserved = Convert.ToBoolean(reader["projectReserved"]);
                pProperty.TimesReserved = Convert.ToInt32(reader["projectTimesReserved"]);
                pProperty.Requirements = reader["projectRequirements"].ToString();
                pProperty.StartPeriod = Convert.ToDateTime(reader["projectStartPeriod"]);
                pProperty.EndPeriod = Convert.ToDateTime(reader["projectEndPeriod"]);
                pProperty.LifeSpan = Convert.ToDateTime(reader["projectLifeSpan"]);
                if (!Convert.IsDBNull(reader["DivisionID"]))
                {
                    pProperty.DivisionId = Convert.ToInt32(reader["DivisionID"]);
                }
                else
                {
                    pProperty.DivisionId = 0;
                }
                if (!Convert.IsDBNull(reader["SubEducationID"]))
                {
                    pProperty.SubEducationId = Convert.ToInt32(reader["SubEducationID"]);
                }
                else
                {
                    pProperty.SubEducationId = 0;
                }
                allPP.Add(pProperty);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allPP;
    }

    public int MaxProjectID()
    {
        int result = 0;

        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT MAX(ID) AS MaxID FROM tblProject", conn);


        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.Read())
        {
            result = Convert.ToInt32(reader["MaxID"]);
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return result;
    }

    public int Reservation(int prId, int times, bool reserved)
    {
        int result = 0;

        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("UPDATE tblProject SET projectReserved = @prReserved, projectTimesReserved = @prTimesReserved WHERE ID = @ID", conn);

        cmd.Parameters.Add("@ID", MySqlDbType.Int32);
        cmd.Parameters["@ID"].Value = prId;

        cmd.Parameters.Add("@prReserved", MySqlDbType.Bit);
        cmd.Parameters["@prReserved"].Value = reserved;

        cmd.Parameters.Add("@prTimesReserved", MySqlDbType.Int32);
        cmd.Parameters["@prTimesReserved"].Value = times;

        conn.Open();
        cmd.Connection = conn;

        result = cmd.ExecuteNonQuery();

        cmd.Dispose();
        conn.Close();

        return result;
    }

    public ProjectBLL()
    {

    }
}